package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.BaseJdbcDao;
import com.bringway.p2p.dao.YzmDao;
@Repository
public class YzmDaoImpl extends BaseJdbcDao implements YzmDao{
	
	private Logger logger = Logger.getLogger(YzmDaoImpl.class);
	@SuppressWarnings("deprecation")
	@Override
	public boolean insertYzm(String mobile, String yzm, String yzmType) {
		String sql = "INSERT INTO T_FM_SYS_YZM(ID,SJHM,YZM,YZM_TYPE,YZM_STATE) VALUES(uuid(),:sjhm,:yzm,:yzmType,:yzmZt)";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("sjhm", mobile);
		paramMap.put("yzm", yzm);
		paramMap.put("yzmType", yzmType);
		paramMap.put("yzmZt", "0");
		logger.info("insertYzm sql is : " + sql);
		logger.info("insertYzm paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap)>0;
	}

	@Override
	public boolean isEffectiveYzm(String mobile, String yzm, String yzmType) {
		StringBuffer sql =new StringBuffer();
		sql.append("SELECT COUNT(*) FROM (select * from t_fm_sys_yzm where yzm_type =:yzmType ");
		sql.append( "and sjhm =:mobile order by yzm_fssj desc limit 1) t1 where t1.sjhm =:mobile and ");
		sql.append( "t1.yzm =:yzm and t1.yzm_state = '0' and t1.yzm_fssj >= DATE_SUB(now(),INTERVAL 10 MINUTE)");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("mobile", mobile);
		paramMap.put("yzm", yzm);
		paramMap.put("yzmType", yzmType);
		logger.info("isEffectiveYzm sql is : " + sql.toString());
		logger.info("isEffectiveYzm paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap)>0;
	}

	@Override
	public boolean verifyYzm(String mobile, String yzm, String yzmType) {
		StringBuffer sql =new StringBuffer();
		sql.append("SELECT ID FROM (select * from t_fm_sys_yzm where yzm_type =:yzmType and sjhm =:mobile ");
		sql.append("order by yzm_fssj desc limit 1) t1 where t1.sjhm =:mobile and t1.yzm =:yzm and ");
		sql.append("t1.yzm_state = '0' and t1.yzm_fssj >= DATE_SUB(now(),INTERVAL 10 MINUTE)");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("mobile", mobile);
		paramMap.put("yzm", yzm);
		paramMap.put("yzmType", yzmType);
		logger.info("verifyYzm sql is : " + sql.toString());
		logger.info("verifyYzm paramMap is : " + paramMap);
		String ID=this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<String>() {
			@Override
			public String extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				String id=null;
				while (resultSet.next()) {
					id=resultSet.getString("ID");
				}
				return id;
			}
		});
		// 更新验证码状态
		if (StringUtils.isNotEmpty(ID) && !"null".equals(ID)) {
			String sql2 = "update t_fm_sys_yzm set yzm_state = 1 where ID=:id AND SJHM =:mobile AND yzm =:yzm";
			Map<String, String> paramMap1 = new HashMap<>();
			paramMap1.put("id", ID);
			paramMap1.put("mobile", mobile);
			paramMap1.put("yzm", yzm);
			logger.info("verifyYzm sql is : " + sql.toString());
			logger.info("verifyYzm paramMap is : " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql2, paramMap1);
			return true;
			}
		return false;
	}

	@Override
	public boolean getYzmCount(String mobile, String yzmType) {
		StringBuffer sql =new StringBuffer();
		sql.append("select count(*) from t_fm_sys_yzm where yzm_type =:yzmType and SJHM =:mobile and");
		sql.append( " yzm_fssj >= DATE_SUB(now(),INTERVAL 1 MINUTE) order by yzm_fssj desc limit 1");
		Map<String, String> paramMap = new HashMap<>();
		paramMap.put("mobile", mobile);
		paramMap.put("yzmType", yzmType);
		logger.info("getYzmCount sql is : " + sql.toString());
		logger.info("getYzmCount paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap)>0;
	}

	@Override
	public String getRandNum(int charCount) {String charValue = "";
	for (int i = 0; i < charCount; i++) {
		char c = (char) (randomInt(0, 10) + '0');
		charValue += String.valueOf(c);
	}
	return charValue;
	}
	/**
	 * @作者 js  
	 * @方法名 randomInt  
	 * @功能 TODO 获取随机数字
	 * @param from
	 * @param to
	 * @return
	 */
	private int randomInt(int from, int to) {
		Random r = new Random();
		return from + r.nextInt(to - from);
	}

	@Override
	public String getSjhmByGyh(String gyh) {
		String sql="select SJHM from t_fm_sys_user where GYH='"+gyh+"'";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				String sjhm=null;
				while (resultSet.next()) {
					sjhm=resultSet.getString("SJHM");
				}
				if ("null".equals(sjhm)||StringUtils.isBlank(sjhm)) {
					sjhm=null;
				}
				return sjhm;
			}
		});
	}
	

}
